import pymysql
import random
from datetime import datetime
from faker import Faker
faker1 = Faker(locale='zh_cn')
# 作业：
# 1、通过python连接pymysql数据库，封装类方法
"""
class DB:
    def __init__(self,host,user,password,databases,cursorclass):
        self.host = host
        self.user = user
        self.password = password
        self.cursorclass = cursorclass
        self.databases = databases

    def connect(self):
        self.db = pymysql.connect(host=self.host, user=self.user, password=self.password, database=self.databases,cursorclass=self.cursorclass)
        self.cursor = self.db.cursor()

    def close(self):
        self.cursor.close()
        self.db.close()

    def SeachOne(self, sql):
         self.connect()
        try:
            self.cursor.execute(sql)
            result = self.cursor.fetchone()
            self.close()
            return result
        except Exception as e:
            self.db.rollback()
            self.close()
            print(f"查询数据库失败，请检查！异常错误为：{e}")
            return None

    def SeachAll(self, sql):
        self.connect()
        try:
            self.cursor.execute(sql)
            result = self.cursor.fetchall()
            self.close()
            return result
        except Exception as e:
            self.db.rollback()
            self.close()
            print(f"查询数据库失败，请检查！异常错误为：{e}")
            return None

        def __edit(self, sql):
            self.connect()
            try:
                self.cursor.execute(sql)
                self.db.commit()
                self.close()
                return 1
            except Exception as e:
                self.db.rollback()
                self.close()
                print(f"sql语句执行异常，请检查！异常错误为：{e}")
                return 0

        def insert(self, sql):
            self.__edit(sql)

        def update(self, sql):
            self.__edit(sql)

        def delete(self, sql):
            self.__edit(sql)
if __name__ == '__main__':
    # 通过DB类，创建了d1对象
    d1 = DB(host='121.41.51.49',user='root',password='Aa010903!',databases = 'crmeb',cursorclass=pymysql.cursors.DictCursor) # pymysql.cursors.DictCursor 设置返回值为字典对象
    # 查询数据库一条
    print(d1.SeachOne('select * from eb_user_group'))
    # 查询数据库多条
    print(d1.SeachAll('select * from eb_user_group'))
    # 修改数据
    d1.update('update eb_user_group set group_name="琳姐" where id=1')
    print(d1.SeachAll('select * from eb_user_group')) # 确认下有没有修改成功
    # 新增数据
    d1.insert('insert into eb_user_group values(6,"张三")')
    print(d1.SeachAll('select * from eb_user_group'))  # 确认下有没有新增成功
    # 删除数据
    d1.delete('delete from eb_user_group where id=6')
    print(d1.SeachAll('select * from eb_user_group'))  # 确认下有没有删除成功"""

# 1.1、随机插入100条数据到数据库表，包括姓名、年龄、家庭住址、身份证号码
# db = pymysql.connect(host="121.40.86.4",user='root',password='Aa010903!',database='lin',cursorclass=pymysql.cursors.DictCursor)
# cursor = db.cursor()
# for i in range(100):
#     name = faker1.name()
#     address = faker1.address()
#     phone = faker1.ssn()
#     age = str(int(datetime.now().strftime('%Y%m%d')) - int(phone[-12:-4]))[0:2]
#     cursor.execute(f'insert into people(name,age,address,phone_number) values("{name}",{age},"{address}","{phone}")')
# db.commit()
# with open("lin.txt",'a',encoding='utf-8') as f:
#     f.write(f'{datetime.now().strftime("%Y-%m-%D %H:%M:%S")}---随机插入100条数据\n')
# db.close()

# 1.2、查询全部的数据
# sql = "select *from people"
# cursor.execute(sql)
# print(cursor.fetchall())
# with open("lin.txt",'a',encoding='utf-8') as f:
#     f.write(f'{datetime.now().strftime("%Y-%m-%D %H:%M:%S")}---查询全部数据\n')

# 1.3、删除指定一条数据
# cursor.execute('select *from people where name="罗欢"')
# with open("log.txt",'a',encoding='utf-8') as f:
#     f.write(f'{datetime.now().strftime("%Y-%m-%D %H:%M:%S")}---删除数据{cursor.fetchone()}\n')
# sql = 'delete from people where name="罗欢"'
# cursor.execute(sql)
# db.commit()

# 1.4、封装批量删除方法，可以删除多条
# def delete_sql(name_list):
#     for i in name_list:
#         cursor.execute(f'select *from people where name="{i}"')
#         with open("log.txt", 'a', encoding='utf-8') as f:
#             f.write(f'{datetime.now().strftime("%Y-%m-%D %H:%M:%S")}---删除数据{cursor.fetchone()}\n')
#         cursor.execute(f'delete from people where name="{i}"')
#     db.commit()
#     db.close()
# delete_sql(['廖峰','夏淑英'])

# 1.5、可以指定增加单独一条数据到表里
# cursor.execute('insert into people(name,age,address,phone_number) values("黄勃",60,"辽宁省沈阳市皇姑区1793号","213409196523451235")')
# db.commit()
# sql='select *from people where name="黄渤"'
# cursor.execute(sql)
# with open("log.txt",'a',encoding='utf-8') as f:
#     f.write(f'{datetime.now().strftime("%Y-%m-%D %H:%M:%S")}---插入数据{cursor.fetchone()}\n')  # 没有数据返回
# cursor.close()
# db.close()


# 以上每个操作步骤要写日志信息到log.txt里









# 2、做一个学生点名系统，系统里支持：存入学生、更改学生、删除学生、点到学生姓名方法
# 存入学生（姓名、年龄、家庭地址、手机号）： 存入成功后，提示xxx存入成功
# db = pymysql.connect(host="121.40.86.4",user='root',password='Aa010903!',database='lin',cursorclass=pymysql.cursors.DictCursor)
# cursor = db.cursor()
# try:
#     name = input('请输入学生姓名：')
#     age = input('请输入学生年龄：')
#     address = input('请输入学生地址：')
#     phone = input('请输入学生手机号：')
#     sql = f'insert into student values("{name}",{age},"{address}","{phone}")'
#     cursor.execute(sql)
#     db.commit()
#     print(f"{name}存入成功！")
# except Exception as e:
#     print(e)
#     print("存入失败!")
# finally:
#     db.close()


# 更改学生（姓名、年龄、家庭地址、手机号）：可以修改学生内容的任意一个字段
# name = input("请输入要修改的学生姓名：")
# sql = f'select *from student where name="{name}"'
# cursor.execute(sql)
# if cursor.fetchone()!=None:
#     print("姓名\t年龄\t地址\t手机号")
#     user_in = input("请输入要修改的字段：")
#     if user_in=='姓名':
#         new_name = input("请输入修改后的名字：")
#         update = f'update student set name="{new_name}" where name = "{name}"'
#         cursor.execute(update)
#     elif user_in=='年龄':
#         new_age = input("请输入修改后的年龄：")
#         update = f'update student set age={new_age} where name = "{name}"'
#         cursor.execute(update)
#     elif user_in == '地址':
#         new_address = input("请输入修改后的地址：")
#         update = f'update student set address="{new_address}" where name ="{name}"'
#         cursor.execute(update)
#     elif user_in=='手机号':
#         new_phone = input("请输入修改后的手机号：")
#         update = f'update student set phone="{new_phone}" where name = "{name}"'
#         cursor.execute(update)
#     else:
#         print("字段名错误请重新选择")
#     db.commit()
#     db.close()
#     print("修改成功！")
# else:
#     print("您输入的学生不存在")


# 删除学生：删除的学生将不在展示
# name = input("请输入要删除的学生姓名：")
# cursor.execute('select name from student')
# list_name = cursor.fetchall()
# for i in range(len(list_name)):
#     if list_name[i]['name']==name:
#         sql = f'delete from student where name="{name}"'
#         cursor.execute(sql)
#         db.commit()
#         db.close()
#         print("删除成功")

# 展示全部学生：可以看到全部的学生姓名
# sql = 'select * from student'
# cursor.execute(sql)
# print(cursor.fetchall())
# db.close()

# 学生姓名：随机抽取任意一个存在的学生信息，然后打印：到！。举例： 刘闯到！
# sql = 'select name from student'
# cursor.execute(sql)
# dict1 = cursor.fetchall()
# i = random.randint(0,len(dict1)-1)
# print(dict1[i]['name']+"到！")

#
# 以上内容，需要连接数据库，每次的数据都需要增、删、改、查去做
#
# 3、类作业，统计镰刀和斧头的数量，要使用类方法
# class Scythe:
#     def __init__(self,num):
#         self.num = num
#     def get_num(self):
#         return self.num
# class Axe:
#     def __init__(self,num):
#         self.num = num
#     def get_num(self):
#         return self.num
# if __name__ == '__main__':
#     s  =Scythe(12)
#     a = Axe(15)
#     sum = s.get_num()+a.get_num()
#     print(sum)
#
# 4、新建学生成绩管理系统，随机生成100个 学生信息插入到数据库：
# 学生信息组成：学号、学生姓名、英语成绩、数学成绩、语文成绩、班级号（班级号有：1809A，1809B，1810A，1810B）
#
# 成绩划分为：优秀，良好，中等，及格，不及格。良好比中等好。
#
# 优秀：90—100之间（包括90）
# 良好：80-90
# 中等：70-80
# 及格：60-70
# 不合格：分数低于60分。
#
# 成绩管理系统提供以下方法：
# 《1》 查询所有英语成绩为优秀的、良好的、中等、及格和不合格的学生姓名和班级号
class Student_system:
    def connevt(self):
        self.db =
        self.cursor =self.db.cursor
    def close(self):
        
# eng_1 = 'select name,class_id from s_score where eng_score>=90'
# cursor.execute(eng_1)
# print("英语成绩优秀")
# print(cursor.fetchall())
# eng_2 = 'select name,class_id from s_score where eng_score >=80 and eng_score<90'
# cursor.execute(eng_2)
# print("英语成绩良好")
# print(cursor.fetchall())
# eng_3 = 'select name,class_id from s_score where eng_score >=70 and eng_score<80'
# cursor.execute(eng_3)
# print("英语成绩中等")
# print(cursor.fetchall())
# eng_4 = 'select name,class_id from s_score where eng_score >=60 and eng_score<70'
# cursor.execute(eng_4)
# print("英语成绩及格")
# print(cursor.fetchall())
# eng_5 = 'select name,class_id from s_score where eng_score<60'
# cursor.execute(eng_5)
# print("英语成绩不及格")
# print(cursor.fetchall())
# cursor.close()
# db.close()

# 《2》 查询1809A，1809B，1810A，1810B每个班级的数学成绩，并且是降序返回
# sql_2 = 'select math_score from s_score order by class_id, math_score desc'     # 先按照班级排序再按照成绩排序
# sql_02 = 'select math_score from s_score order by math_score desc'  # 直接按照成绩降序排列
# cursor.execute(sql_2)
# print(cursor.fetchall())
# cursor.close()
# db.close()

# 《3》 查询1809A，1809B，1810A，1810B每个班级的总人数，并且由大到小排序返回
# sql_3 = 'select class_id,count(*) as "人数" from s_score group by class_id order by count(*) desc'
# cursor.execute(sql_3)
# print(cursor.fetchall())
# cursor.close()
# db.close()

# 《4》 查询英语、数学、语文成绩不合格的学生姓名和对应的班级号，按照不合格的人数，由大到小排序
# sql_4 = 'select name,class_id from s_score where eng_score<60 or math_score<60 or chinese_score<60 group by class_id,name'
# cursor.execute(sql_4)
# print(cursor.fetchall())
# cursor.close()
# db.close()
# 5、定义一个图书管理系统类，图书可以新增、修改、删除，要求新增、修改、删除的图书，都要出现在数据库中
# 新增：图书名、作者名称、新建年月日
# for i in range(5):
#     b_name = faker1.bank()
#     z_name = faker1.name()
#     date1 = '2020-08-19'
#     cursor.execute(f'insert into book values("{b_name}","{z_name}","{date1}")')
# db.commit()
# db.close()


# 修改：图书名、作者名称、新建年月日
# name = input("请输入要修改的图书名称：")
# sql = f'select *from book where book_name="{name}"'
# cursor.execute(sql)
# if cursor.fetchone()!=None:
#     print("图书名\t作者名\t新建年月日")
#     user_in = input("请输入要修改的字段：")
#     if user_in=='图书名':
#         new_name = input("请输入修改后的图书名：")
#         update = f'update student set book_name="{new_name}" where book_name = "{name}"'
#         cursor.execute(update)
#     elif user_in=='作者名':
#         new_name1 = input("请输入修改后的作者名：")
#         update = f'update student set writer_name={new_name1} where book_name = "{name}"'
#         cursor.execute(update)
#     elif user_in == '新建年月日':
#         new_c_time= input("请输入修改后的新建年月日：")
#         update = f'update student set c_time="{new_c_time}" where book_name ="{name}"'
#         cursor.execute(update)
#     else:
#         print("字段名错误请重新选择")
#     db.commit()
#     cursor.close()
#     db.close()
#     print("修改成功！")
# else:
#     print("您输入的学生不存在")


# 删除：图书删掉，且不在展示
# d_name = input("请输入要删除的图书姓名：")
# sql = f'delete from book where book_name="{d_name}"'
# cursor.execute(sql)
# db.commit()
# cursor.close()
# db.close()



# 查看：全量查询、精确查询、模糊查询需要用户传入形参来控制返回结果
# cursor.execute('select * from book')
#
# name_in = input("请输入要查找的图书名：")
# # 精确查询
# cursor.execute(f'select *from book where book_name ="{name_in}"')
# # 模糊查询
# cursor.execute(f'select *from book where book_name like "%{name_in}%"')
# cursor.close()
# db.close()